USE MDW_CONTROL
GO
IF OBJECT_ID('[ssas].[populate_olap_query_log_table]') IS NOT NULL	
	DROP PROCEDURE [ssas].[populate_olap_query_log_table] 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


create PROCEDURE [ssas].[populate_olap_query_log_table] (
	@mdw_database_name nvarchar(128) = null
	,@InstanceName nvarchar(128) = null
	,@DatabaseName nvarchar(128) = null
	,@DurationThreshold int = null
	,@CPUTimeThreshold int = null
	,@begin_time datetime = '2008-01-01T00:00:00'
	,@end_time datetime = '2009-12-31T18:08:27'
    , @Debug bit = 0
)
AS
BEGIN
    SET NOCOUNT ON
	
	-- Declare variables
    DECLARE @SQLExecString nvarchar(4000)

    -- Construct query
	    SET @SQLExecString = 

N' 
	INSERT INTO ' + @mdw_database_name + '.[ssas].[OlapQueryLog]

	SELECT
		db.DatabaseName as MSOLAP_Database
		, mg.MeasureGroupName as MSOLAP_ObjectPath
		, u.NTCanonicalUserName as MSOLAP_User
		, sc.SubCubeName as Dataset
		, qev.StartDateTime as StartTime
		, qesv.StorageEngineDuration as Duration
	FROM ' + @mdw_database_name + '.[SSAS].[SSASQuerySubCubeEvents]  qesv
	JOIN ' + @mdw_database_name + '.[ssas].[SSASQueryExecutionEvents] qev
		ON qev.QueryExecutionID = qesv.QueryExecutionID
	JOIN ' + @mdw_database_name + '.[SSAS].[SSASSubCube] sc
		ON qesv.SubCubeID = sc.SubCubeID
	JOIN ' + @mdw_database_name + '.[SSAS].[SSASMeasureGroup] mg
		ON 	mg.MeasureGroupID = qesv.MeasureGroupID
	JOIN ' + @mdw_database_name + '.[SSAS].[SSASCube] AS c 
		ON c.CubeID = mg.CubeID
	JOIN ' + @mdw_database_name + '.[SSAS].[SSASDatabase] AS db 
		ON db.DatabaseID = c.DatabaseID
	JOIN ' + @mdw_database_name + '.[SSAS].[SSASInstance] AS i 
		ON i.InstanceID = db.InstanceID	
	JOIN ' + @mdw_database_name + '.[ssas].[SSASQueryExecution] as qe
		ON qe.QueryExecutionID = qesv.QueryExecutionID
	JOIN ' + @mdw_database_name + '.[ssas].[SSASUser] as u
		ON u.SSASUserID = qe.SSASUserID
	WHERE qev.NumberAggregations = 0 
		and Duration > ' + CAST(@DurationThreshold AS nvarchar(20)) + ' 
		and CPUTime > ' + CAST(@CPUTimeThreshold AS nvarchar(20)) + '
		and i.InstanceName = ''' + @InstanceName + '''
		and db.DatabaseName = ''' + @DatabaseName + '''
		and 
		qev.StartDateTime BETWEEN COALESCE(' + '''' + CAST(@begin_time AS varchar(30)) + ''', ''1/1/1900'') 
					AND COALESCE(' + '''' + CAST(@end_time AS varchar(30)) + ''', ''12/31/9999'')  
	GROUP BY qev.QueryID
  		, db.DatabaseName
		, i.InstanceName
		, u.NTCanonicalUserName
		, sc.SubCubeName
		, qev.StartDateTime
		, qesv.StorageEngineDuration
		, mg.MeasureGroupName
		'     
	
	IF @Debug = 1
	    PRINT @SQLExecString
	ELSE
	    EXEC (@SQLExecString)
 
END;

GO

/* Test

DECLARE	@return_value int

EXEC	@return_value = [ssas].[populate_olap_query_log_table]
		@mdw_database_name = N'mdw_r900_new',
		@InstanceName = N'r900-04',
		@DatabaseName = N'ms_sales',
		@DurationThreshold = 5000,
		@CPUTimeThreshold = 10000,
		@begin_time = N'2008-01-01T00:00:00',
		@end_time = N'2008-10-01T18:08:27',
		@Debug = 0

*/

